import os
from datetime import datetime
from typing import Generator, Optional
from urllib.parse import quote_plus

from sqlalchemy import create_engine, Column, String, Text, DateTime
from sqlalchemy.orm import declarative_base, sessionmaker, Session


def _build_mysql_url() -> Optional[str]:
    host = os.getenv('MYSQL_HOST','192.168.0.101')
    database = os.getenv('MYSQL_DB','work')
    if not host or not database:
        return None

    user = os.getenv('MYSQL_USER', 'root')
    password = os.getenv('MYSQL_PASSWORD', 'Zly198707.')
    port = os.getenv('MYSQL_PORT', '3306')
    query = os.getenv('MYSQL_QUERY', '').lstrip('?')

    user_part = quote_plus(user)
    password_part = f":{quote_plus(password)}" if password else ""
    query_part = f"?{query}" if query else ""

    return f"mysql+pymysql://{user_part}{password_part}@{host}:{port}/{database}{query_part}"


def _get_default_db_url() -> str:
    mysql_url = _build_mysql_url()
    if mysql_url:
        return mysql_url

    # 默认写入容器内 /app/data 或源码根目录 data
    base_dir_candidates = [
        os.path.join(os.getcwd(), 'data'),  # 源码运行
        os.path.join(os.path.dirname(__file__), '../../data'),  # 备选
        '/app/data',  # 容器运行
    ]
    for d in base_dir_candidates:
        try:
            os.makedirs(d, exist_ok=True)
            return f"sqlite:///{os.path.abspath(os.path.join(d, 'app.db'))}"
        except Exception:
            continue
    # 回退：内存库（不持久化）
    return "sqlite:///:memory:"


DATABASE_URL = os.getenv('DATABASE_URL', _get_default_db_url())

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {},
    pool_pre_ping=True,
    future=True,
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, future=True)

Base = declarative_base()


class ProjectConfigRecord(Base):
    __tablename__ = 'project_configs'

    id = Column(String(64), primary_key=True)
    step = Column(String(64), nullable=True)
    data = Column(Text, nullable=False)  # 原始 JSON 字符串
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)


def init_db() -> None:
    Base.metadata.create_all(bind=engine)


def get_db() -> Generator[Session, None, None]:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
